package cn.hpclub.server.service.campus;/**
 * Created by zenghl on 2016/8/23.
 */

import cn.hpclub.server.constant.Constant;
import cn.hpclub.server.model.recommend.Recommend;
import cn.hpclub.server.util.InterfaceUtil;
import cn.hpclub.server.util.ObjectUtil;
import cn.hpclub.server.util.excel.ExportUtils;
import com.jfinal.plugin.activerecord.Db;
import com.jfinal.plugin.activerecord.Record;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import java.io.FileNotFoundException;
import java.io.IOException;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.Map;
import java.util.concurrent.ConcurrentHashMap;

/**
 * @FileName BroadbandOpenOrderService
 * @Author zhl
 * @Description
 * @Date 2016-08-23 12:32
 * @CopyRight ZTE Corporation
 */
public class BroadbandOpenOrderService {

    private static final Logger logger = LoggerFactory.getLogger(BroadbandOpenOrderService.class);
    public static BroadbandOpenOrderService service = new BroadbandOpenOrderService();



    public String getSqlHead(){
        String sqlHead = "SELECT orgType.orgIDTmp,orgType2.orgIDTmp2,oc.class_name,(case member_type when 1 then '店长' when 2 then '店员' when 3 then '泛店员' else '未知' end) as memberType,mo.mobile,ca.area_name,cc.region_name,boo.order_id,boo.cust_name,boo.con_phone,boo.address,(case boo.sale_type when 1 then 'APP' when 2 then '分享页面' when 3 then '好友圈' else '未知' end) as saletype," +
                "boo.cust_address,boo.bd_name as broadband_name," +
                "boo.business_code,boo.cfm_login,boo.add_product_list,boo.login_no,boo.user_group,boo.created_time,o.DisplayName, " +
                "IF(orgType.orgIDTmp || orgType2.orgIDTmp2 , '是', '否') AS is_accounts_manager ";
        return sqlHead;
    }


    public String getSqlExceptSelect(ConcurrentHashMap<String, String> searchInfo){
        int topId = InterfaceUtil.getIdTop();
        String sqlExceptSelect = " FROM broadband_open_order boo " +
                " LEFT JOIN cmcc_area ca ON boo.region_id=ca.region_id  and  boo.region_value = ca.area_code " +
                " LEFT JOIN cmcc_city cc ON boo.region_id = cc.region_id   " +
                " LEFT JOIN member_org mo ON boo.user_id = mo.id "+
                " LEFT JOIN (SELECT id AS orgIDTmp FROM `organization` where (parentid=" + topId +
                " OR parentid IN (select id FROM organization WHERE  parentid=" + topId +
                " ))) AS orgType ON boo.org_id = orgType.orgIDTmp"+
                " LEFT JOIN (SELECT org.id AS orgIDTmp2, orgcls.WebsiteId AS WebsiteIdType FROM organization org," +
                " organization_class orgcls WHERE org.WebsiteId = orgcls.id AND org.enabled=1 AND orgcls.enabled=1 AND " +
                "( orgcls.WebsiteId LIKE '6%' OR orgcls.WebsiteId = '1000' OR orgcls.WebsiteId = '4800')) " +
                "AS orgType2 ON boo.org_id = orgType2.orgIDTmp2"+
                " LEFT JOIN organization o ON boo.user_group=o.NumCode " +
                " LEFT JOIN organization_class oc ON o.WebsiteId=oc.id " +
                " WHERE 1=1 AND (!ISNULL(boo.order_id) AND LENGTH(boo.order_id)>0) ";

        for(Map.Entry<String, String> item : searchInfo.entrySet()){
            sqlExceptSelect += buildQueryForCount(item);
        }
        sqlExceptSelect += " ORDER BY boo.created_time DESC ";
        return sqlExceptSelect;
    }



    private String buildQueryForCount(Map.Entry<String, String> item){
        if("start_time".equalsIgnoreCase(item.getKey())){
            String start_time = "'" + item.getValue().replaceAll(" ", "") + " 00:00:00'";
            return " and boo.created_time >=" + start_time + " ";
        }
        if("end_time".equalsIgnoreCase(item.getKey())){
            String end_time = "'" + item.getValue().replaceAll(" ", "") + " 23:59:59'";
            return " and boo.created_time <=" + end_time + " ";
        }
        /*if("org_id".equalsIgnoreCase(item.getKey())){
            return " and boo.org_id =" + item.getValue() + " ";
        }*/
        if(item.getKey().equals("org_id")){
            return " and boo.org_id in("
                    + InterfaceUtil.getChlidOrgIdSql(Integer.parseInt(item.getValue()), Constant.MAX_ORG_LEVEL, true)
                    + ") ";
        }
        if(item.getKey().startsWith("isMgr")){
            if("1".equals(item.getValue())){
                return " and ((orgType.orgIDTmp is not null) or (orgType2.orgIDTmp2 is not null)) ";
            } else if("0".equals(item.getValue())){
                return " and ((orgType.orgIDTmp is  null) and (orgType2.orgIDTmp2 is null)) ";
            }else{
                return "";
            }

        }
        if(item.getKey().startsWith("memberType")){
            if(!"0".equals(item.getValue())) {
                return " and mo.member_type =" + item.getValue();
            }else{
                return  "";
            }
        }
        return " and boo." + item.getKey() + " like '%" + item.getValue() + "%'";
    }





    /**
     * @describe
     * @param searchInfo 查询条件参数列表
     * @return SXSSFWorkbook
     */
    public SXSSFWorkbook getExcel(ConcurrentHashMap<String, String> searchInfo){
        SXSSFWorkbook workbook = null;
        String sql = getSqlHead() + getSqlExceptSelect(searchInfo);
        List<Record> seq = Db.find(sql);

        String[] excelHeaders = new String[]{"订单号","联系人姓名","联系电话","地市","区县","装机地址","详细地址","宽带名称","资费","宽带账号",
                "互联网电视产品","办理工号","渠道编码","渠道名称","渠道类型","办理来源","店员电话","店员身份","是否客户经理","创建时间"};
        ExportUtils exportUtils = new ExportUtils(excelHeaders);
        String[] keys = new String[]{"order_id","cust_name","con_phone","region_name","area_name","address","cust_address","broadband_name"
                ,"business_code","cfm_login","add_product_list","login_no","user_group","DisplayName","class_name","saletype","mobile","memberType","is_accounts_manager","created_time"};

       try {
            exportUtils.exportExcel(keys,seq,"yyyy-MM-dd HH:mm:ss",1);//此处最后一个参数表示从第一行开始填入数据（表头为第0行）
            workbook = exportUtils.getSXSSFWorkbook();
        } catch (FileNotFoundException e) {
            logger.error("BroadbandOpenOrderService.getExcel(ConcurrentHashMap<String, String> searchInfo) happened an FileNotFoundException,error message:"+e.getMessage());
        } catch (IOException ex) {
            logger.error("BroadbandOpenOrderService.getExcel(ConcurrentHashMap<String, String> searchInfo) happened an IOException,error message:"+ex.getMessage());
        }
        return workbook;
    }
}
